package com.situ.library;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.situ.JDBC.JDBCUtil;

public class RecordDao {

	/**
	 * 执行insert-sql插入一条记录
	 * 
	 * @param model
	 * @return
	 * @throws SQLException
	 */
	public int insert(RecordModel model) {// add save 面向对象的开发
		String sql = "insert into record(b_id,c_id,type,timeDate)values(?,?,?,?)";
		Connection conn = JDBCUtil.getConnection();
		PreparedStatement ps = null;
		if (conn == null)
			return 0;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, model.getBId());
			ps.setString(2, model.getCId());
			ps.setInt(3, model.getType());
			ps.setObject(4, model.getTimeDate());
			return ps.executeUpdate(); // 执行sql语句，并返回影响的行数
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0;

	}

	/**
	 * 根据逻辑主键修改记录其他全部字段
	 * 
	 * @param model
	 * @return
	 */
	public String update(RecordModel model) {
		String sql = "update record set c_id=?,type=?,timeDate=? where b_id=?";
		Connection conn = JDBCUtil.getConnection();
		if (conn == null)
			return null;
		PreparedStatement ps = null;
		try {
			ps = conn.prepareStatement(sql);
			ps.setString(1, model.getCId());
			ps.setInt(2, model.getType());
			ps.setObject(3, model.getTimeDate());
			ps.setString(5, model.getBId());
			return ps.executeUpdate() + "";
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps);
		}
		return null;
	}


	/**
	 * 根据多个查询条件 - 执行查询sql，返回结果构成的实体类集合
	 * 
	 */
	public List<RecordModel> selectList(RecordModel model) {
		// 要进行字符串拼接，使用StringBuffer/StringBuilder
		StringBuffer sql = new StringBuffer("select b_id,c_id,type,timeDate from record ");
		List<Object> vals = appendWhere(sql, model);
		Connection conn = JDBCUtil.getConnection();
		if (conn == null)
			return null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<RecordModel> list = new ArrayList<>();
		try {
			ps = conn.prepareStatement(sql.toString());
			for (int i = 0; i < vals.size(); i++)
				ps.setObject(i + 1, vals.get(i));
			rs = ps.executeQuery();
			RecordModel p = null;
			while (rs.next()) {// ORM（把数据库的表的信息转换成数据库的过程）
				p = new RecordModel(null, null, null);
				p.setBId(rs.getString("b_id"));
				p.setCId(rs.getString("c_id"));
				p.setType(rs.getInt("type"));
				p.setTimeDate(rs.getString("timeDate"));
				list.add(p);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(conn, ps, rs);
		}
		return list;
	}

	private List<Object> appendWhere(StringBuffer sql, RecordModel model) {
		sql.append(" where 1=1 ");
		List<Object> vals = new ArrayList<>();
		String bid = model.getBId();
		if (bid != null && !bid.trim().isEmpty()) { // !code.trim().isEmpty()：不是空字符串（即空格一类的）
			sql.append(" and b_id = ? "); // 不加空格会导致前后连在一起导致执行失败
			vals.add(bid.trim());
		}
		String cid = model.getCId();
		if (cid != null && !cid.trim().isEmpty()) {
			sql.append(" and name = ? ");
			vals.add(cid.trim());
		}
		Integer type = model.getType();
		if (type != null) {
			sql.append(" and age = ? ");
			vals.add(type);
		}

		String timeDate = model.getTimeDate();
		if (timeDate != null && timeDate != null) {
			sql.append(" and timeDate > ? ");
			vals.add(timeDate);
		}

		return vals;
	}
}
